package com.rocksea.app.db.SonicWave;

import android.content.Context;
import android.database.Cursor;
import android.os.Environment;

import android.database.sqlite.SQLiteDatabase;
import com.litesuits.orm.LiteOrm;
import com.litesuits.orm.db.DataBaseConfig;
import com.litesuits.orm.db.assit.QueryBuilder;
import com.litesuits.orm.db.assit.SQLiteHelper;
import com.litesuits.orm.db.assit.WhereBuilder;
import com.litesuits.orm.db.impl.CascadeSQLiteImpl;
import com.litesuits.orm.db.model.ColumnsValue;
import com.litesuits.orm.db.model.ConflictAlgorithm;
import com.rocksea.app.db.DbCenter;
import com.rocksea.app.db.uploadflag.UploadFlag;
import com.rocksea.app.interfaces.MyExecuteCallBack;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * Created by 吴志华 on 2017/7/28.
 *
 *
 */

public class ScDbHelper implements SQLiteHelper.OnUpdateListener{

    private static LiteOrm mDbHelper;
    private static ScDbHelper mScDbHelper;

    private ScDbHelper(Context context)
    {
        DataBaseConfig config = new DataBaseConfig(context);
        config.dbName = Environment.getExternalStorageDirectory().getPath()+"/RailDatabase/db/SonicWave.db";
        config.dbVersion = 1;
        config.onUpdateListener = this;
        mDbHelper = CascadeSQLiteImpl.newInstance(config);
        //mDbHelper.setDebugged(true);
        SonicWave sonicWave = new SonicWave();
        sonicWave.SonicWaveDatas = new ArrayList<>();
        sonicWave.SonicWaveDatas.add(new SonicWave.SonicWaveData());
        sonicWave.SonicWaveFaces = new ArrayList<>();
        sonicWave.SonicWaveFaces.add(new SonicWave.SonicWaveFace());
        mDbHelper.insert(sonicWave);
        mDbHelper.delete(sonicWave);

    }

    @Override
    public void onUpdate(SQLiteDatabase sqLiteDatabase, int i, int i1)
    {
        if (i != i1)
        {
            switch (i)
            {
                case 1:
                    String sql = "Alter table "+"SonicWave"+" add column "+"inspectionFormId"+"TEXT ";
                    sqLiteDatabase.execSQL(sql);
                    sql = "Alter table "+"SonicWave"+" add column "+"pileId"+" TEXT ";
                    sqLiteDatabase.execSQL(sql);
                    sql = "Alter table "+"SonicWave"+" add column "+"inspectionFormNum"+"TEXT ";
                    sqLiteDatabase.execSQL(sql);
                    //// 修改字段类型
                    sql = "ALTER TABLE SonicWaveData RENAME TO temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    ////  创建新表
                    sql = "CREATE TABLE SonicWaveData(id INTEGER PRIMARY KEY AUTOINCREMENT,A REAL ,P REAL ,PSD REAL ,RecvHeight REAL ,T REAL ,T1 INTEGER ,V REAL ,creatTime INTEGER ,curValidData INTEGER ,delay REAL ,depth INTEGER ,gain REAL ,isValid INTEGER DEFAULT 0 ,sWMaxI INTEGER ,sampleTime TEXT ,sectionId TEXT ,sonicWaveId INTEGER ,upLoadTime INTEGER DEFAULT 0 ,uuid TEXT ,waveData BLOB )";
                    sqLiteDatabase.execSQL(sql);
                    sql = "INSERT INTO SonicWaveData SELECT * FROM  temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    sql = "DROP TABLE temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    break;

                case 2:
                    //// 修改字段类型
                    sql = "ALTER TABLE SonicWaveData RENAME TO temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    ////  创建新表
                    sql = "CREATE TABLE SonicWaveData(id INTEGER PRIMARY KEY AUTOINCREMENT ,A REAL ,P REAL ,PSD REAL ,RecvHeight REAL ,T REAL ,T1 INTEGER ,V REAL ,creatTime INTEGER ,curValidData INTEGER ,delay REAL ,depth INTEGER ,gain REAL ,isValid INTEGER DEFAULT 0 ,sWMaxI INTEGER ,sampleTime TEXT ,sectionId TEXT ,sonicWaveId INTEGER ,upLoadTime INTEGER DEFAULT 0 ,uuid TEXT ,waveData BLOB )";
                    sqLiteDatabase.execSQL(sql);
                    sql = "INSERT INTO SonicWaveData SELECT * FROM  temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    sql = "DROP TABLE temp_table_name";
                    sqLiteDatabase.execSQL(sql);
                    break;
            }
        }
    }

    public static ScDbHelper getInstance(Context context){
        if (mScDbHelper==null)
        {
            synchronized (ScDbHelper.class){
                mScDbHelper = new ScDbHelper(context);
            }
        }
        return mScDbHelper;
    }


    private CopyOnWriteArrayList<String> sonicWaveUUID = new CopyOnWriteArrayList<>();

    public void save(SonicWave mScBaseInfo)
    {
        QueryBuilder<SonicWave> sonicWaveFaceQueryBuilder = new QueryBuilder<>(SonicWave.class);
        sonicWaveFaceQueryBuilder.whereEquals("uuid",mScBaseInfo.uuid);
        sonicWaveUUID.clear();

        //查询数据库 id
        String sql = "SELECT c.uuid from SonicWaveData as c inner join SonicWaveFace as b on b.baseInfoId = ? and b.sectionId = c.sectionId ";
        Cursor cursor =  mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{mScBaseInfo.uuid});
        if (cursor.moveToFirst())
        {
            do {
                String uuid = cursor.getString(0);
                sonicWaveUUID.add(uuid);
            }while (cursor.moveToNext());
        }
        cursor.close();

        int count = (int) mDbHelper.queryCount(sonicWaveFaceQueryBuilder);
        if (count == 0)
        {
            mDbHelper.save(mScBaseInfo);
            if (mScBaseInfo.SonicWaveFaces != null)
            {
                insertWaveFace(mScBaseInfo.SonicWaveFaces,false);
            }
            if (mScBaseInfo.SonicWaveDatas!=null)
            {
                insertWaveData(mScBaseInfo.SonicWaveDatas,mScBaseInfo.uuid);
            }
        }
        else
        {
            if (!isSonicWaveUpload(mScBaseInfo.uuid))
            {
                WhereBuilder sonicWaveWhereBuilder = new WhereBuilder(SonicWave.class);
                sonicWaveWhereBuilder.where("uuid = ?",new Object[]{mScBaseInfo.uuid});
                mDbHelper.delete(sonicWaveWhereBuilder);
                mDbHelper.save(mScBaseInfo);
            }
        }
    }

    private boolean isSonicWaveUpload(String baseInfoId)
    {
        String sql = "select upLoadTime from SonicWave where uuid = ?";
        Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{baseInfoId});
        if (cursor.moveToNext())
        {
            long value = cursor.getLong(cursor.getColumnIndex("upLoadTime"));
            cursor.close();
            return value > 0;
        }
        cursor.close();
        return false;
    }

    private  <T>void save(T t)
    {
        mDbHelper.save(t);
    }

    private synchronized boolean isHavedData(String uuid)
    {
        for (String s : sonicWaveUUID)
        {
            if (s.equals(uuid))
            {
                return true;
            }
        }
        return false;
    }


    public void insertWaveData(List<SonicWave.SonicWaveData> sonicWaveDatas,String uuid)
    {
        if (sonicWaveUUID.size() == 0)
        {
            String sql = "SELECT c.uuid from SonicWaveData as c inner join SonicWaveFace as b on b.baseInfoId = ? and b.sectionId = c.sectionId ";
            Cursor cursor =  mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{uuid});
            if (cursor.moveToFirst())
            {
                do {
                    String id = cursor.getString(0);
                    sonicWaveUUID.add(id);
                }while (cursor.moveToNext());
            }
            cursor.close();
        }

        if (!isHavedData(sonicWaveDatas.get(0).uuid))
        {
            mDbHelper.save(sonicWaveDatas);
            ArrayList<UploadFlag> uploadFlags = new ArrayList<>();
            for (int i = 0; i < sonicWaveDatas.size(); i++)
            {
                save(sonicWaveDatas.get(i));
                //保存上传标识符表
                uploadFlags.add(new UploadFlag(sonicWaveDatas.get(i).sectionId,sonicWaveDatas.get(i).uuid));
            }
            DbCenter.mUploadFlagDbHelper.insertBySql(uploadFlags);
            for (int i = 0; i < sonicWaveDatas.size(); i++)
            {
                //保存上传标识符表
                sonicWaveUUID.add(sonicWaveDatas.get(i).uuid);
            }
        }
    }

    /**
     * @param flag 是否需要清空uuidList集合
     * */

    public void insertWaveFace(List<SonicWave.SonicWaveFace> sonicWaveFaces,boolean flag)
    {
        if (flag)
            sonicWaveUUID.clear();
        //String baseInfoId = null;
        for (int i = 0; i < sonicWaveFaces.size(); i++)
        {
            //baseInfoId = sonicWaveFaces.get(i).baseInfoId;
            //判断数据库中是否存在
            QueryBuilder<SonicWave.SonicWaveFace> sonicWaveFaceQueryBuilder = new QueryBuilder<>(SonicWave.SonicWaveFace.class);
            sonicWaveFaceQueryBuilder.whereEquals("sectionId",sonicWaveFaces.get(i).sectionId);
            int count = (int) mDbHelper.queryCount(sonicWaveFaceQueryBuilder);
            if (count == 0)
                save(sonicWaveFaces.get(i));
            else
            {
                if (!isSonicWaveFaceUpload(sonicWaveFaces.get(i).sectionId))
                {
                    WhereBuilder dataWhereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
                    dataWhereBuilder.where("sectionId LIKE ?",new Object[]{sonicWaveFaces.get(i).sectionId});
                    mDbHelper.delete(dataWhereBuilder);
                    save(sonicWaveFaces.get(i));
                }
            }
        }
    }

    public ArrayList<SonicWave> query()
    {
        return mDbHelper.query(SonicWave.class);
    }

    public long queryTotalCounts()
    {
        return mDbHelper.queryCount(SonicWave.SonicWaveData.class);
    }



    public void delete(String baseInfoId)
    {
        ArrayList<SonicWave.SonicWaveFace> faces = (ArrayList<SonicWave.SonicWaveFace>) querySectionByBaseInfoId(baseInfoId);
        for (int i = 0; i < faces.size(); i++)
        {
            WhereBuilder dataWhereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
            dataWhereBuilder.andEquals("sectionId",faces.get(i).sectionId);
            mDbHelper.delete(dataWhereBuilder);
            //删除上传标识数据库
            DbCenter.mUploadFlagDbHelper.deletebySetionId(faces.get(i).sectionId);
        }
        WhereBuilder sectionWhereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        sectionWhereBuilder.andEquals("baseInfoId",baseInfoId);
        mDbHelper.delete(sectionWhereBuilder);

        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.class);
        whereBuilder.andEquals("uuid",baseInfoId);
        mDbHelper.delete(whereBuilder);


    }

    public SonicWave querySonicWave(String baseInfoId)
    {
        SonicWave sonicWaves = DbCenter.mScDbHelper.queryByBaseInfoId(baseInfoId);
        if (sonicWaves!=null)
        {
            if (sonicWaves.SonicWaveFaces == null)
            {
                sonicWaves.SonicWaveFaces = (ArrayList<SonicWave.SonicWaveFace>) DbCenter.mScDbHelper.querySectionByBaseInfoId(baseInfoId);
            }
            // 根据剖面id查询数据
            if (sonicWaves.SonicWaveFaces != null) {
                ArrayList<SonicWave.SonicWaveData> sonicWaveData = new ArrayList<>();
                for (int i = 0; i < sonicWaves.SonicWaveFaces.size(); i++) {
                    List<SonicWave.SonicWaveData> sonicWaveDatas = DbCenter.mScDbHelper.querySonicWaveData(sonicWaves.SonicWaveFaces.get(i).sectionId);
                    for (int n = 0; n < sonicWaveDatas.size(); n++) {
                        if (sonicWaveDatas.get(n).isValid == 0) {
                            sonicWaveData.add(sonicWaveDatas.get(n));
                        }
                    }
                }
                sonicWaves.SonicWaveDatas = sonicWaveData;
            }
        }
        return sonicWaves;
    }

    public SonicWave queryByBaseInfoId(String baseInfoId)
    {
        QueryBuilder<SonicWave> queryBuilder = new QueryBuilder<>(SonicWave.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.class);
        whereBuilder.where("uuid LIKE ?",baseInfoId);
        queryBuilder.where(whereBuilder);
        ArrayList<SonicWave> sonicWaves = mDbHelper.query(queryBuilder);
        if (sonicWaves.size()==0)
            return null;
        return sonicWaves.get(0);
    }

    public SonicWave queryById(int id)
    {
        return mDbHelper.queryById(id,SonicWave.class);
    }

    public void makeAdUpLoad(String baseInfoId)
    {
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"isLoad","upLoadTime"},new String[]{"1",String.format("%d",new Date().getTime())});
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.class);
        whereBuilder.andEquals("uuid",baseInfoId);
        mDbHelper.update(whereBuilder,columnsValue, ConflictAlgorithm.Fail);
    }


    public void makeBaseInfoAsUpLoad(String baseInfoId)
    {
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"isLoad","upLoadTime"},new String[]{"1",String.format("%d",new Date().getTime())});
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.andEquals("uuid",baseInfoId);
        mDbHelper.update(whereBuilder,columnsValue, ConflictAlgorithm.Fail);
    }

    public void makeNodeAsUpLoad(String baseInfoId)
    {
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"isLoad","upLoadTime"},new String[]{"1",String.format("%d",new Date().getTime())});
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.andEquals("uuid",baseInfoId);
        mDbHelper.update(whereBuilder,columnsValue, ConflictAlgorithm.Fail);

    }

    public void makeSectionAsUpLoad(String baseInfoId) {
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"isLoad","upLoadTime"},new String[]{"1",String.format("%d",new Date().getTime())});
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        whereBuilder.andEquals("uuid",baseInfoId);
        mDbHelper.update(whereBuilder,columnsValue, ConflictAlgorithm.Fail);
    }

    public List<SonicWave.SonicWaveFace> querySectionByBaseInfoId(String baseInfoId)
    {
        QueryBuilder<SonicWave.SonicWaveFace> queryBuilder = new QueryBuilder<>(SonicWave.SonicWaveFace.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        whereBuilder.where("baseInfoId LIKE ?",baseInfoId);
        queryBuilder.where(whereBuilder);
        List<SonicWave.SonicWaveFace> sonicWaveFaces = mDbHelper.query(queryBuilder);
        //EventBus.getDefault().post(sonicWaveFaces);
        return sonicWaveFaces;
    }

    private boolean isSonicWaveFaceUpload(String sectionId)
    {
        String sql = "select upLoadTime from SonicWaveFace where sectionId = ?";
        Cursor cursor  = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{sectionId});
        if (cursor.moveToNext())
        {
            long value = cursor.getLong(cursor.getColumnIndex("upLoadTime"));
            cursor.close();
            return value > 0;
        }
        cursor.close();
        return false;
    }


    public List<SonicWave.SonicWaveFace> querySectionByBaseInfoId(String baseInfoId, MyExecuteCallBack<List<SonicWave.SonicWaveFace>> callBack)
    {
        QueryBuilder<SonicWave.SonicWaveFace> queryBuilder = new QueryBuilder<>(SonicWave.SonicWaveFace.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        whereBuilder.where("baseInfoId LIKE ?",baseInfoId);
        queryBuilder.where(whereBuilder);
        List<SonicWave.SonicWaveFace> sonicWaveFaces = mDbHelper.query(queryBuilder);
        //EventBus.getDefault().post(sonicWaveFaces);
        callBack.onSuccess(sonicWaveFaces);
        return sonicWaveFaces;
    }



    public List<SonicWave.SonicWaveData> querySonicWaveData(String sectionId)
    {
        QueryBuilder<SonicWave.SonicWaveData> queryBuilder = new QueryBuilder<>(SonicWave.SonicWaveData.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.where("sectionId LIKE ?",sectionId);
        queryBuilder.where(whereBuilder);
        queryBuilder.orderBy("depth");
        return mDbHelper.query(queryBuilder);
    }

    public void makeAsIsValid(String uuid, int i)
    {
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.andEquals("uuid",uuid);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"isValid"},new Object[]{i});
        mDbHelper.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }

    public Collection<? extends SonicWave.SonicWaveData> queryValidSonicWaveData(String sectionId)
    {
        QueryBuilder<SonicWave.SonicWaveData> queryBuilder = new QueryBuilder<>(SonicWave.SonicWaveData.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.where("sectionId LIKE ?",sectionId);
        whereBuilder.and();
        whereBuilder.where("isValid",0);
        queryBuilder.where(whereBuilder);
        return  mDbHelper.query(queryBuilder);
    }

    public void insertSonicWaveServiceId(String uuid,int returnCode)
    {
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.class);
        whereBuilder.andEquals("uuid",uuid);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"serviceId"},new Object[]{returnCode});
        mDbHelper.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }



    public void makeAsSonicWaveFaceUpLoad(String uuid)
    {
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        whereBuilder.andEquals("baseInfoId",uuid);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"upLoadTime"},new Object[]{new Date().getTime()});
        mDbHelper.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }

    public void makeAsSonicWaveDataUpLoad(String uuid)
    {
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveData.class);
        whereBuilder.andEquals("uuid",uuid);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"upLoadTime"},new Object[]{new Date().getTime()});
        mDbHelper.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }

    /**
     * 一次查询完整的声测数据
     *
     * */
    public SonicWave querySonicWaveFragmet(String baseInfoId)
    {
        QueryBuilder<SonicWave> queryBuilder = new QueryBuilder<>(SonicWave.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.class);
        whereBuilder.where("uuid LIKE ?",baseInfoId);
        queryBuilder.where(whereBuilder);
        ArrayList<SonicWave> sonicWaves = mDbHelper.query(queryBuilder);
        if (sonicWaves.size()==0)
            return null;
        if (sonicWaves.get(0).SonicWaveFaces==null)
        {
            //查询剖面
            List<SonicWave.SonicWaveFace> sonicWaveFaces = mScDbHelper.querySectionByBaseInfoId(baseInfoId);
            sonicWaves.get(0).SonicWaveFaces = (ArrayList<SonicWave.SonicWaveFace>) sonicWaveFaces;
            //查询测点数据
            if (sonicWaves.get(0).SonicWaveDatas==null)
            {
                sonicWaves.get(0).SonicWaveDatas = new ArrayList<>();
                for (int i = 0; i < sonicWaves.get(0).SonicWaveFaces.size(); i++)
                {
                    List<SonicWave.SonicWaveData> sonicWaveDatas = mScDbHelper.querySonicWaveData(sonicWaves.get(0).SonicWaveFaces.get(i).sectionId);
                    sonicWaves.get(0).SonicWaveDatas.addAll(sonicWaveDatas);
                }
            }
        }
        return sonicWaves.get(0);
    }

    public ArrayList<Integer> queryDistinctData(String sectionId)
    {
        String sql = "select depth from SonicWaveData where sectionId=? AND isValid = ? group by depth having count(id)>1";
        Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{sectionId,"0"});
        ArrayList<Integer> depths = new ArrayList<>();
        while (cursor.moveToNext())
        {
            int depth = cursor.getInt(cursor.getColumnIndex("depth"));
            depths.add(depth);
        }
        cursor.close();
        return depths;
    }

    public ArrayList<SonicWave.SonicWaveData> queryDistinWaveData(ArrayList<Integer> distinctDepth,String sectionId)
    {
        ArrayList<SonicWave.SonicWaveData> resultWaveData = new ArrayList<>();

        if (distinctDepth!=null&&distinctDepth.size()!=0) {

            for (int i = 0; i < distinctDepth.size(); i++)
            {
                QueryBuilder<SonicWave.SonicWaveData> sonicWaveDataQueryBuilder = new QueryBuilder<>(SonicWave.SonicWaveData.class)
                        .where("sectionId =?",sectionId)
                        .whereAppendAnd()
                        .where("depth =?",distinctDepth.get(i));
                ArrayList<SonicWave.SonicWaveData> waveData =  mDbHelper.query(sonicWaveDataQueryBuilder);
                for (SonicWave.SonicWaveData sonicWaveData : waveData) {
                    resultWaveData.add(sonicWaveData);
                }
            }
        }
        return resultWaveData;
    }

    public ArrayList<SonicWave.SonicWaveData> queryDistinWaveData(String sectionId)
    {
        ArrayList<SonicWave.SonicWaveData> resultWaveData = new ArrayList<>();
        ArrayList<Integer> distinctDepth = queryDistinctData(sectionId);
        if (distinctDepth!=null&&distinctDepth.size()!=0)
        {
            for (int i = 0; i < distinctDepth.size(); i++)
            {
                QueryBuilder<SonicWave.SonicWaveData> sonicWaveDataQueryBuilder = new QueryBuilder<>(SonicWave.SonicWaveData.class)
                        .where("sectionId =?",sectionId)
                        .whereAppendAnd()
                        .where("depth =?",distinctDepth.get(i));
                ArrayList<SonicWave.SonicWaveData> waveData =  mDbHelper.query(sonicWaveDataQueryBuilder);
                for (SonicWave.SonicWaveData sonicWaveData : waveData)
                    resultWaveData.add(sonicWaveData);
            }
        }
        return resultWaveData;
    }


    public boolean oneKeyToUnValid(ArrayList<String> sectionIds)
    {
        int keyCount = 0;
        String sql = "select depth from SonicWaveData where sectionId = ? AND isValid = 0 group by depth having count(id)>1";
        for (int i = 0; i < sectionIds.size(); i++) {
            Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{sectionIds.get(i)});
            while (cursor.moveToNext())
            {
                int depth = cursor.getInt(cursor.getColumnIndex("depth"));
                String querySampleDepthSql = "select uuid from SonicWaveData where depth = ? AND sectionId = ? ORDER BY creatTime";
                Cursor depthCursor = mDbHelper.getWritableDatabase().rawQuery(querySampleDepthSql,new String[]{String.valueOf(depth),sectionIds.get(i)});
                ArrayList<String> uuids = new ArrayList<>();

                while (depthCursor.moveToNext())
                {
                    String uuid  = depthCursor.getString(depthCursor.getColumnIndex("uuid"));
                    uuids.add(uuid);
                }
                depthCursor.close();
                if (uuids.size()==0)
                    continue;
                for (int m = 0; m < uuids.size(); m++)
                {
                    if (m<uuids.size()-1)
                    {
                        keyCount++;
                        String makeUnValidSql = "UPDATE SonicWaveData SET isValid = ? WHERE uuid = ?";
                        mDbHelper.getWritableDatabase().execSQL(makeUnValidSql,new String[]{String.valueOf(1),uuids.get(m)});
                    }
                }
            }
            cursor.close();
        }
        return keyCount!=0;
    }

    public ArrayList<String> querySectionId(String upLoadBaseInfoId)
    {
        String sql = "select sectionId from SonicWaveFace where baseInfoId = ?";
        Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{upLoadBaseInfoId});
        ArrayList<String> sectionIds = new ArrayList<>();
        while (cursor.moveToNext())
        {
            String sectionId = cursor.getString(cursor.getColumnIndex("sectionId"));
            sectionIds.add(sectionId);
        }
        cursor.close();
        return sectionIds;
    }

    public long queryPlanId(String upLoadBaseInfoId)
    {
        String sql = "select pileId from SonicWave where uuid = ?";
        Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{upLoadBaseInfoId});
        while (cursor.moveToNext())
        {
            return cursor.getLong(cursor.getColumnIndex("pileId"));
        }
        cursor.close();
        return -1;
    }

    public long queryServiceId(String baseInfoId)
    {
        String sql = "select serviceId from SonicWave where uuid = ?";
        Cursor cursor = mDbHelper.getWritableDatabase().rawQuery(sql,new String[]{baseInfoId});
        while (cursor.moveToNext())
        {
            long serviceId = cursor.getLong(cursor.getColumnIndex("serviceId"));
            cursor.close();
            return serviceId;
        }
        cursor.close();
        return -1;
    }

    public ArrayList<SonicWave.SonicWaveFace> queryUnLoadSection(String baseInfoId)
    {
        QueryBuilder<SonicWave.SonicWaveFace> queryBuilder = new QueryBuilder<>(SonicWave.SonicWaveFace.class);
        WhereBuilder whereBuilder = new WhereBuilder(SonicWave.SonicWaveFace.class);
        whereBuilder.where("baseInfoId LIKE ?",baseInfoId);
        whereBuilder.where("upLoadTime LIKE =",0);
        queryBuilder.where(whereBuilder);
        return mDbHelper.query(queryBuilder);
    }
}
